Imports System.Data
Imports System.Xml
Imports System.Data.SqlClient
Imports AppsUtility.EncDec
Public Class APdataLogic
    Dim util As New AppsUtility.EncDec
    Public strCon As String = util.toDecrypt(System.Configuration.ConfigurationSettings.AppSettings("SQLString"))
    Public Function GetSupplierCode(ByVal SupID As Integer) As String
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.SupCode from Supplier a " & _
                "where a.SupID='" & SupID & "'"
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim CusCode As String
        Try
            CusCode = sqlCmd.ExecuteScalar()
        Catch ex As SqlException

        End Try
        sqlCon.Close()
        GetSupplierCode = CusCode
    End Function
    Public Function GetSuppliers(ByVal WhereClause As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * " & _
                "from Supplier " & WhereClause & " order by SupName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("SUPPLIER")
        Try
            da.Fill(ds, "SUPPLIER")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetSuppliers = ds
    End Function
    Public Function GetSupplierID(ByVal SupCode As String) As Integer
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.SupID from Supplier a " & _
                "where a.SupCode='" & SupCode & "'"
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim CusID As Integer
        Try
            CusID = sqlCmd.ExecuteScalar()
        Catch ex As SqlException

        End Try
        sqlCon.Close()
        GetSupplierID = CusID
    End Function
    Public Function SupplierProductCodeExist(ByVal CompanyID As Integer, ByVal ProductCode As String, ByVal SupID As Integer) As Boolean
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select count(*) ProdCount from Product a inner join ProductSupplier b " & _
        "on a.ProductID=b.ProdID and b.CompanyID=" & CompanyID & " and b.SupID=" & SupplierID & _
        " where a.ProductCode='" & ProductCode & "'"
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCon.Open()
        Dim ProdCount As Integer
        ProdCount = CInt(sqlCmd.ExecuteScalar)
        sqlCon.Close()
        If ProdCount > 0 Then
            Return True
        Else
            Return False
        End If
    End Function
    Public Function GetSupplierProducts(ByVal CompanyID As Integer, ByVal SupplierID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.ProductID,a.ManufProductCode,a.ProductCode,a.ShortDescriptione,a.LongDescriptione," & _
                "a.FirstPrice from Product a inner join ProductSupplier b " & _
                "on a.ProductID=b.ProdID and b.CompanyID=" & CompanyID & " and b.SupID=" & SupplierID
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("PRODUCT")
        Try
            da.Fill(ds, "PRODUCT")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetSupplierProducts = ds
    End Function
    Public Function SearchSupplierProducts(ByVal CompanyID As Integer, ByVal SupplierID As Integer, ByVal SearchColumn As String, ByVal SearchString As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.ProductID,a.ManufProductCode,a.ProductCode,a.ShortDescriptione,a.LongDescriptione," & _
                "a.FirstPrice from Product a inner join ProductSupplier b " & _
                "on a.ProductID=b.ProdID and b.CompanyID=" & CompanyID & " and b.SupID=" & SupplierID & _
                " where " & SearchColumn & " like '" & SearchString & "%' order by " & SearchColumn
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("PRODUCT")
        Try
            da.Fill(ds, "PRODUCT")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        SearchSupplierProducts = ds
    End Function
    Public Function GetGroupDetails(ByVal GroupID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from SupplierGroup where GroupID=" & GroupID
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("GROUP")
        Try
            da.Fill(ds, "GROUP")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetGroupDetails = ds
    End Function
    Public Function GetSupplierGroups() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.GroupID,a.GroupName " & _
                    "from SupplierGroup a " & _
                    " order by GroupName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("SUPPLIERGROUPS")
        Try
            da.Fill(ds, "SUPPLIERGROUPS")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        Dim dRow As DataRow
        dRow = ds.Tables(0).NewRow()
        dRow("GroupID") = 0
        dRow("GroupName") = "ALL GROUPS"
        ds.Tables(0).Rows.Add(dRow)
        sqlCon.Close()
        GetSupplierGroups = ds
    End Function
    Public Function GetAllCompany() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select CompanyID,CompanyName from Company " & _
                 "order by CompanyName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAllCompany = ds
    End Function

    Public Function GetAllDivisions(ByVal CompId As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select DepartmentID,DepartmentName  from Department d, Company C where c.CompanyID=d.CompanyID and c.CompanyID=" & CompId & " order by d.DepartmentID "
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("DIV")
        Try
            da.Fill(ds, "DIV")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAllDivisions = ds
    End Function

    Public Function GetCompanyCrLimitPrompt() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select CompanyID,CompanyName,0 CreditLimit,0 ValidCompany,0 CREDITPERIOD from Company " & _
                 "order by CompanyName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetCompanyCrLimitPrompt = ds
    End Function
    Public Function GetCompanyShowrooms(ByVal CompanyID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select DepartmentID,DepartmentName from Department " & _
                 "where CompanyID=" & CompanyID & " and ShowroomFlag=1 order by DepartmentName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("SHOWROOM")
        Try
            da.Fill(ds, "SHOWROOM")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        Dim dRow As DataRow
        dRow = ds.Tables(0).NewRow()
        dRow("DepartmentID") = 0
        dRow("DepartmentName") = "ALL SHOWROOMS"
        ds.Tables(0).Rows.Add(dRow)
        sqlCon.Close()
        GetCompanyShowrooms = ds
    End Function
    Public Function GetProductSupplierPrompt(ByVal CompanyID As Integer, ByVal ProdID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.SupID,a.SupName," & _
                "isnull(b.ValidSup,0) ValidSup " & _
                "from Supplier a left outer join ProductSupplier b " & _
                "on a.SupID=b.SupID where a.CompanyID=" & CompanyID & " and  " & _
                "b.ProdID=" & ProdID & " " & _
                "union all " & _
                "select a.SupID,a.SupName,0 ValidSup " & _
                "from Supplier a where a.CompanyID=3 " & _
                "and a.SupID not in " & _
                "(select a.SupID from Supplier a left outer join ProductSupplier b " & _
                "on a.SupID=b.SupID where a.CompanyID=" & CompanyID & " and  " & _
                "b.ProdID=" & ProdID & ") " & _
                "order by a.SupName"

        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("SUPPLIER")
        Try
            da.Fill(ds, "SUPPLIER")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetProductSupplierPrompt = ds
    End Function
    Public Function GetCompanySuppliers(ByVal CompanyID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        'strsql = "select a.SupID,a.SupName " & _
        '        "from Supplier a,CompanySupplier b " & _
        '        "where b.CompanyID=" & CompanyID & " and a.SupID=b.SupID " & _
        '        "order by a.SupName"
        strsql = "select a.SupID,a.SupName " & _
                "from Supplier a order by a.SupName"

        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("SUPPLIER")
        Try
            da.Fill(ds, "SUPPLIER")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetCompanySuppliers = ds
    End Function
    Public Function GetSuppliersByCompany(ByVal CompanyID As Integer, ByVal GroupID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        'If GroupID = 0 Then
        '    strsql = "select a.* " & _
        '            "from Supplier a,CompanySupplier b " & _
        '            "where b.CompanyID=" & CompanyID & " and a.SupID=b.SupID " & _
        '            "order by a.SupName"
        'Else
        '    strsql = "select a.* " & _
        '            "from Supplier a,CompanyCustomer b " & _
        '            "where b.CompanyID=" & CompanyID & " and a.SupID=b.CusID " & _
        '            "and a.SupGroupID=" & GroupID & " " & _
        '            "order by a.SupName"
        'End If
        If GroupID = 0 Then
            strsql = "select a.* " & _
                    "from Supplier a order by a.SupName"
        Else
            strsql = "select a.* " & _
                    "from Supplier a where a.SupGroupID=" & GroupID & " " & _
                    "order by a.SupName"
        End If

        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("SUPPLIER")
        Try
            da.Fill(ds, "SUPPLIER")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetSuppliersByCompany = ds
    End Function
    Public Function GetSupplierDetails(ByVal SupplierID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.* from Supplier a " & _
                "where a.SupID=" & SupplierID
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("SUPPLIER")
        Try
            da.Fill(ds, "SUPPLIER")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetSupplierDetails = ds
    End Function
    Public Function GetAllGroups() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.GroupID,a.GroupCode,a.GroupName " & _
                "from SupplierGroup a  " & _
                " order by GroupName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("GROUP")
        Try
            da.Fill(ds, "GROUP")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAllGroups = ds
    End Function
    Public Function GetGroupLevels() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select LevelID,LevelName from GroupLevel " & _
                 "order by LevelName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("LEVEL")
        Try
            da.Fill(ds, "LEVEL")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        Dim dRow As DataRow
        dRow = ds.Tables(0).NewRow()
        dRow("LevelID") = 0
        dRow("LevelName") = "ALL LEVELS"
        ds.Tables(0).Rows.Add(dRow)
        sqlCon.Close()
        GetGroupLevels = ds
    End Function
    Public Function ProcessCusProdMatrix(ByVal InvXml As String) As String
        Dim Xdoc As XmlDocument = New XmlDocument
        Xdoc.LoadXml(InvXml)
        Dim RootNode As XmlElement = Xdoc.DocumentElement
        Dim CompanyID As Integer = RootNode.SelectSingleNode("InvoiceHead/CompanyID").InnerText
        Dim CustomerID As String = RootNode.SelectSingleNode("InvoiceHead/CustomerID").InnerText
        Dim nodeList As XmlNodeList = RootNode.GetElementsByTagName("InvLine")
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim sqlCmd As New SqlCommand()
        sqlCmd.Connection = sqlCon
        sqlCon.Open()

        Dim i As Integer
        For i = 0 To nodeList.Count - 1
            Dim ProdID As String = nodeList.Item(i).ChildNodes(0).InnerText
            Dim ProdCode As String = nodeList.Item(i).ChildNodes(1).InnerText
            strsql = "Insert into CustomerProduct (CompanyID,CustomerID,ProductID,CustomerProdCode) " & _
                    "Values (" & CompanyID & "," & CustomerID & "," & ProdID & "," & _
                    "'" & ProdCode & "')"
            sqlCmd.CommandText = strsql
            Try
                sqlCmd.ExecuteNonQuery()
            Catch ex As SqlException
                InsertError("ProcessCusProdMatrix", ex.LineNumber, ex.Number, ex.ToString())
                Return "ERROR"
            End Try
        Next i
        sqlCon.Close()
        Return "OK"
    End Function
    Public Sub UpdateGroupData(ByVal GroupID As Integer, ByVal GroupCode As String, _
                ByVal GroupName As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)

        Dim strsql As String
        strsql = "Update SupplierGroup set " & _
            "GroupCode='" & GroupCode & "',GroupName='" & GroupName & "'" & _
            " where GroupID=" & GroupID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Try
            sqlCmd.ExecuteNonQuery()
        Catch ex As SqlException
            InsertError(ex.Source, ex.LineNumber, ex.Number, ex.ToString())
            Exit Sub
        End Try
    End Sub
    Public Function GetNumDefault(ByVal NumericField As String) As String
        If NumericField.ToString.Trim = "" Then
            Return "0"
        Else
            Return NumericField.Replace(",", "")
        End If
    End Function
    Public Sub DeleteSupplier(ByVal SupplierID As Integer)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Delete from Supplier where SupID=" & SupplierID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        strsql = "Delete from ProductSupplier where SupID=" & SupplierID
        sqlCmd.CommandText = strsql
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Public Sub DeleteSupplierGroup(ByVal GroupID As Integer)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Delete from SupplierGroup where GroupID=" & GroupID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Public Sub InsertSupplierGroup(ByVal GroupCode As String, ByVal GroupName As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Insert into SupplierGroup (GroupCode,GroupName) " & _
                "values('" & GroupCode & "','" & GroupName & "')"
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Public Function InsertSupplierDetails(ByVal CompanyID As Integer, ByVal SupCode As String, ByVal GroupID As Integer, ByVal SupName As String, _
                ByVal Adr1 As String, ByVal Adr2 As String, ByVal Dadr1 As String, ByVal Dadr2 As String, _
                ByVal Email As String, ByVal Website As String, ByVal Phone As String, _
                ByVal Fax As String, ByVal Contact As String, ByVal CusXml As String) As String
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        sqlCon.Open()
        Dim CusID As Integer


        strsql = "Select SupID from Supplier where SupCode='" & SupCode & "' "


        Dim sqlCmd As New SqlCommand(strsql, sqlCon)
        sqlCmd.CommandText = strsql

        Dim mSupID As Integer = 0
        Try
            mSupID = sqlCmd.ExecuteScalar()

        Catch ex As Exception

        End Try
        If mSupID > 0 Then
            Return "EXIST"
            Exit Function
        End If

        strsql = "Insert into Supplier(CompanyID,SupCode,SupGroupID,SupName," & _
                "SupAdr1,SupAdr2,SupAdr3,SupAdr4,SupEmail,SupWebUrl,SupTelephone,SupFax,SupContact) " & _
                "values(" & CompanyID & ",'" & SupCode & "'," & GroupID & "," & _
                "'" & SupName & "','" & Adr1 & "','" & Adr2 & "','" & Dadr1 & "','" & Dadr2 & "'," & _
                "'" & Email & "','" & Website & "','" & Phone & "'," & _
                "'" & Fax & "','" & Contact & "') " & _
                "select @@identity"
        sqlCmd.CommandText = strsql
        Try
            CusID = sqlCmd.ExecuteScalar
        Catch ex As Exception
            Return "ERROR"
        End Try
        Dim Xdoc As XmlDocument = New XmlDocument
        Xdoc.LoadXml(CusXml)
        Dim IncludeCus As Boolean
        Dim ThisCompany As Integer
        Dim CrLimit As Decimal
        Dim CrPeiod As String

        Dim RootNode As XmlElement = Xdoc.DocumentElement
        Dim nodeList As XmlNodeList = RootNode.GetElementsByTagName("InvLine")
        Dim i As Integer
        For i = 0 To nodeList.Count - 1
            IncludeCus = nodeList.Item(i).ChildNodes(1).InnerXml()
            ThisCompany = Integer.Parse(nodeList.Item(i).ChildNodes(0).InnerXml())

            CrLimit = nodeList.Item(i).ChildNodes(2).InnerXml()
            CrPeiod = nodeList.Item(i).ChildNodes(3).InnerXml()

            If IncludeCus = True Then
                strsql = "Insert into CompanySupplier (SupID,CompanyID,CrLimit,CrPeriod) " & _
                        "values(" & CusID & "," & ThisCompany & "," & CrLimit & ",'" & CrPeiod & "')"

                Dim sqlCmd1 As New SqlCommand(strsql, sqlCon)
                sqlCmd1.CommandText = strsql
                sqlCmd1.ExecuteNonQuery()
            End If
        Next i
        sqlCon.Close()
        Return "OK"
    End Function
    Public Sub UpdateSupplierDetails(ByVal SupID As Integer, ByVal SupCode As String, ByVal GroupID As Integer, ByVal SupName As String, _
                ByVal Adr1 As String, ByVal Adr2 As String, ByVal Dadr1 As String, ByVal Dadr2 As String, _
                ByVal Email As String, ByVal Website As String, ByVal Phone As String, _
                ByVal Fax As String, ByVal Contact As String, ByVal CusXml As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim sqlCon2 As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Update Supplier set SupGroupID=" & GroupID & "," & _
                "SupCode='" & SupCode & "',SupName='" & SupName & "',SupAdr1='" & Adr1 & "',SupAdr2='" & Adr2 & "',SupAdr3='" & Dadr1 & "',SupAdr4='" & Dadr2 & "'," & _
                "SupEmail='" & Email & "',SupWebUrl='" & Website & "',SupTelephone='" & Phone & "'," & _
                "SupFax='" & Fax & "',SupContact='" & Contact & "',Trfrd='N' " & _
                "Where SupID=" & SupID
        sqlCon.Open()
        sqlCon2.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Try
            sqlCmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.ToString())
        End Try
        Dim Xdoc As XmlDocument = New XmlDocument
        Xdoc.LoadXml(CusXml)
        Dim IncludeCus As Boolean

        Dim ThisCompany As Integer
        Dim RootNode As XmlElement = Xdoc.DocumentElement
        Dim nodeList As XmlNodeList = RootNode.GetElementsByTagName("InvLine")
        Dim i As Integer
        For i = 0 To nodeList.Count - 1
            IncludeCus = nodeList.Item(i).ChildNodes(1).InnerXml()
            ThisCompany = Integer.Parse(nodeList.Item(i).ChildNodes(0).InnerXml())
            If IncludeCus = True Then
                strsql = "Select count(*) from CompanySupplier where CompanyID=" & ThisCompany & " and SupID=" & SupID
                Dim sqlCmd2 As SqlCommand = New SqlCommand(strsql, sqlCon2)
                If sqlCmd2.ExecuteScalar = 0 Then
                    strsql = "Insert into CompanySupplier (SupID,CompanyID) " & _
                            "values(" & SupID & "," & ThisCompany & ")"
                    sqlCmd.CommandText = strsql
                    sqlCmd.ExecuteNonQuery()
                End If
            Else
                strsql = "Select count(*) from PurchaseOrderHead where PoCompanyID=" & ThisCompany & " and PoSupID=" & SupID
                Dim sqlCmd2 As SqlCommand = New SqlCommand(strsql, sqlCon2)
                If sqlcmd2.ExecuteScalar = 0 Then
                    strsql = "Select count(*) from CompanySupplier where CompanyID=" & ThisCompany & " and SupID=" & SupID
                    sqlCmd2.CommandText = strsql
                    If sqlCmd2.ExecuteScalar > 0 Then
                        strsql = "Delete from CompanySupplier " & _
                                "where SupID=" & SupID & " and CompanyID=" & ThisCompany
                        sqlCmd.CommandText = strsql
                        sqlCmd.ExecuteNonQuery()
                    End If
                End If
            End If
        Next i
        sqlCon.Close()
    End Sub
    Public Sub InsertError(ByVal ErrModule As String, ByVal ErrLine As String, ByVal ErrNumber As String, ByVal ErrDescription As String)
        'On Error Resume Next
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Insert into ErrorLog (ErrModule,ErrLine,ErrNumber,ErrDescription) " & _
                "values('" & ErrModule & "','" & ErrLine & "','" & ErrNumber & "','" & ErrDescription.Replace("'", "") & "')"
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Private Function PrefixInteger(ByVal strVal As String) As String
        If strVal.Trim = "" Then
            Return "0"
        Else
            Return strVal.Replace(",", "")
        End If
    End Function
    Private Function ValidDate(ByVal strVal As String) As String
        If strVal.Trim = "" Then
            Return "0"
        Else
            Dim dd As String
            Dim mm As String
            Dim yyyy As String
            dd = strVal.Substring(0, 2)
            mm = strVal.Substring(3, 2)
            yyyy = strVal.Substring(6, 4)
            strVal = mm & "/" & dd & "/" & yyyy
            Return strVal
        End If
    End Function
    Public Function GetSupplierEmail(ByVal SupplierID As Integer) As String
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Select SupEmail from Supplier where SupID=" & SupplierID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim NextInv As String
        Dim rdr As SqlDataReader
        rdr = sqlCmd.ExecuteReader
        While rdr.Read
            NextInv = CStr(rdr("SupEmail"))
        End While

        sqlCon.Close()
        Return NextInv
    End Function
    Public Function GetCompanyUpdatedCrLimitPrompt(ByVal SupID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.CompanyID,a.CompanyName,0 ValidCompany ,0 CrLimit, 0 CrPeriod " & _
            "from Company a where a.CompanyID not in (select CompanyID from CompanySupplier " & _
            "where SupID=" & SupID & ") " & _
            "union all " & _
            "select a.CompanyID,a.CompanyName,1 ValidCompany,b.CrLimit,b.CrPeriod  " & _
            "from Company a,CompanySupplier b " & _
            "where a.CompanyID=b.CompanyID and b.SupID=" & SupID & " " & _
            "order by companyname "
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetCompanyUpdatedCrLimitPrompt = ds
    End Function
    Public Function GetUserCompanyPrompt(ByVal UID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If UID > 0 Then
            strsql = "select a.CompanyID,a.CompanyName,0 ValidCompany " & _
                "from Company a where a.CompanyID not in (select CompanyID from UserCompany " & _
                "where UserID=" & UID & ") " & _
                "union all " & _
                "select a.CompanyID,a.CompanyName,1 ValidCompany " & _
                "from Company a,UserCompany b " & _
                "where a.CompanyID=b.CompanyID and b.UserID=" & UID & " " & _
                "order by companyname "
        Else
            strsql = "select a.CompanyID,a.CompanyName,0 ValidCompany " & _
                "from Company a order by CompanyName"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetUserCompanyPrompt = ds
    End Function

    Function LoadMainMenuGroups() As SqlDataReader
        Try
            Dim dr As SqlDataReader
            Dim sqlCon As SqlConnection = New SqlConnection(strCon)
            Dim cmd As New SqlCommand("Select MenuId from UserMenu where MenuGroup=0", sqlCon)
            sqlCon.Open()
            Return cmd.ExecuteReader

        Catch ex As Exception
            MsgBox(ex.ToString, 16, "Inventory Manager")
        End Try
    End Function

    Public Function GetUserOptionPrompt(ByVal UID As Integer, ByVal MenuGroupID As Integer, ByVal CompID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If UID > 0 Then
            strsql = "select a.MenuID,a.MenuName,0 ValidMenu " & _
                "from UserMenu a where MenuGroup=" & MenuGroupID & " and a.MenuID not in (select MenuID from UserAccess " & _
                "where UserID=" & UID & " and MenuGroup=" & MenuGroupID & " and CompanyID=" & CompID & ") " & _
                "union all " & _
                "select a.MenuID,a.MenuName,1 ValidMenu " & _
                "from UserMenu a,UserAccess b " & _
                "where a.MenuID=b.MenuID and b.UserID=" & UID & " and MenuGroup=" & MenuGroupID & _
                " and b.CompanyID=" & CompID & " order by a.MenuID "
        Else
            strsql = "select a.MenuID,a.MenuName,0 ValidMenu " & _
                "from UserMenu a where MenuGroup=" & MenuGroupID & " order by MenuID"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetUserOptionPrompt = ds
    End Function
    Public Function GetUserDetails(ByVal UCode As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from UserProfile " & _
            "where UserStatus<>'DELETED' and  UserCode='" & UCode & "'"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
            Dim drow As DataRow
            For Each drow In ds.Tables(0).Rows
                drow("UserPassword") = util.toDecrypt(drow("UserPassword"))
            Next
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetUserDetails = ds
    End Function
    Public Function GetValidUser(ByVal UCode As String, ByVal Pass As String, ByRef Uname As String) As Integer
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String

        Pass = util.toEncrypt(Pass)
        strsql = "select isnull(UserID,0) UserID,UserName from UserProfile " & _
            "where UserStatus='ACTIVE' and ltrim(rtrim(upper(UserCode)))='" & LTrim(RTrim(UCase(UCode))) & "' and ltrim(rtrim(upper(UserPassword)))='" & Pass & "'"

        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim UID As Integer = 0
        Dim rdr As SqlDataReader
        rdr = sqlCmd.ExecuteReader
        While rdr.Read
            UID = CStr(rdr("UserID"))
            Uname = rdr("UserName")
        End While
        sqlCon.Close()
        Return UID
    End Function
    Public Function GetUserList() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from UserProfile order by UserName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        Dim drow As DataRow
        For Each drow In ds.Tables(0).Rows
            'drow("UserCode") = util.toDecrypt(drow("UserCode"))
            'drow("UserName") = util.toDecrypt(drow("UserName"))
            drow("UserPassword") = util.toDecrypt(drow("UserPassword"))
        Next
        GetUserList = ds
    End Function
    Public Function GetUserGroupDetails() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select USERGROUPID,USERGROUPNAME from USERGROUP order by USERGROUPID"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()

        'For Each drow In ds.Tables(0).Rows
        '    'drow("UserCode") = util.toDecrypt(drow("UserCode"))
        '    'drow("UserName") = util.toDecrypt(drow("UserName"))
        '    'drow("UserPassword") = util.toDecrypt(drow("UserPassword"))
        'Next
        GetUserGroupDetails = ds
    End Function
    Public Function GetUserCodeNameList() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select UserID,UserCode+':'+UserName UserName from UserProfile where UserStatus='ACTIVE' and UserRole='1' order by UserName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetUserCodeNameList = ds
    End Function
    Public Function GetUserCompanies(ByVal UID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.CompanyID,a.CompanyName from Company a,UserCompany b " & _
                "where a.CompanyID = b.CompanyID And b.UserID=" & UID & " order by CompanyName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetUserCompanies = ds
    End Function
    Public Function GetUserGroup(ByVal UID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.*,b.UserRole,b.UserGroupID from UserGroup a,UserProfile b " & _
                "where a.UserGroupID=b.UserGroupID and b.UserID=" & UID
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetUserGroup = ds
    End Function
    Public Function GetUserProfileOptions(ByVal UID As Integer, ByVal ThisCompany As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select MenuID from UserAccess where UserID=" & UID & " and CompanyID=" & ThisCompany
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetUserProfileOptions = ds
    End Function
    Public Function IsValidUserOption(ByVal UID As Integer, ByVal ThisCompany As Integer, ByVal OptionID As Integer) As Boolean
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select isnull(count(*),0) from UserAccess " & _
            "where UserID=" & UID & " and CompanyID=" & ThisCompany & " and MenuID=" & OptionID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim mcount As Integer = 0
        Try
            mcount = sqlCmd.ExecuteScalar()
        Catch ex As SqlException

        End Try
        sqlCon.Close()
        If mcount = 0 Then
            Return False
        Else
            Return True
        End If
    End Function
    Public Function GetCompanyDepartment(ByVal CompanyID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select DepartmentID from Department " & _
                "where CompanyID = " & CompanyID
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetCompanyDepartment = ds
    End Function
    Public Function GetCompanyDetails(ByVal CompanyID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from Company " & _
                "where CompanyID = " & CompanyID
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetCompanyDetails = ds
    End Function
    Public Function GetCompanyAddress(ByVal CompanyID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select PostalAddressE,LocationAddressE,Phone,Email from Company " & _
                "where CompanyID =" & CompanyID
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetCompanyAddress = ds
    End Function
    Public Function GetCompanyCenter(ByVal CompanyID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select AccountCenter from Account " & _
                "where CompanyID = " & CompanyID & " and AccountNumber='0000'"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetCompanyCenter = ds
    End Function
    Public Function UpdateUserDetails(ByVal UID As Integer, ByVal UCode As String, ByVal UName As String, ByVal Upass As String, ByVal UStatus As String, ByVal URole As String, ByVal CompanyXml As String, ByVal grp As String) As Integer
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim sqlCon2 As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim SqlCmd As New SqlCommand(strsql, sqlCon2)
        sqlCon.Open()
        sqlCon2.Open()
        Upass = util.toEncrypt(Upass)
        If UID = 0 Then
            strsql = "Insert into UserProfile (UserCode,UserName,UserPassword,UserStatus,UserRole,UserGroupID) " & _
                "values('" & UCode & "','" & UName & "','" & Upass & "','" & UStatus & "'," & URole & "," & grp & ") " & _
                "select @@Identity "

            SqlCmd.CommandText = strsql
            UID = SqlCmd.ExecuteScalar
        Else
            strsql = "Update UserProfile set UserCode='" & UCode & "',UserName='" & UName & "',UserPassword='" & Upass & "'," & _
                    "UserStatus='" & UStatus & "',UserRole='" & URole & "',USERGROUPID =  " & grp & _
                    ",TRFRD='N' where UserID=" & UID

            SqlCmd.CommandText = strsql
            SqlCmd.Connection = sqlCon
            SqlCmd.ExecuteNonQuery()
        End If
        Dim Xdoc As XmlDocument = New XmlDocument
        Xdoc.LoadXml(CompanyXml)
        Dim IncludeCus As Boolean

        Dim ThisCompany As Integer
        Dim RootNode As XmlElement = Xdoc.DocumentElement
        Dim nodeList As XmlNodeList = RootNode.GetElementsByTagName("InvLine")
        Dim i As Integer
        For i = 0 To nodeList.Count - 1
            IncludeCus = nodeList.Item(i).ChildNodes(1).InnerXml()
            ThisCompany = Integer.Parse(nodeList.Item(i).ChildNodes(0).InnerXml())
            If IncludeCus = True Then
                strsql = "Select count(*) from UserCompany where CompanyID=" & ThisCompany & " and UserID=" & UID
                Dim sqlCmd2 As SqlCommand = New SqlCommand(strsql, sqlCon2)
                If sqlCmd2.ExecuteScalar = 0 Then
                    strsql = "Insert into UserCompany (UserID,CompanyID) " & _
                            "values(" & UID & "," & ThisCompany & ")"
                    SqlCmd.CommandText = strsql

                    SqlCmd.ExecuteNonQuery()
                End If
            Else
                strsql = "Select isnull(CompanyUserID,0) from UserCompany where CompanyID=" & ThisCompany & " and UserID=" & UID
                Dim sqlCmd2 As SqlCommand = New SqlCommand(strsql, sqlCon2)
                sqlCmd2.CommandText = strsql
                Dim CUID As Integer = sqlCmd2.ExecuteScalar
                If CUID > 0 Then
                    strsql = "Delete from UserCompany " & _
                            "where UserID=" & UID & " and CompanyID=" & ThisCompany
                    SqlCmd.CommandText = strsql
                    SqlCmd.ExecuteNonQuery()
                    '
                    strsql = "Delete from UserAccess where UserID=" & CUID
                    SqlCmd.CommandText = strsql
                    SqlCmd.ExecuteNonQuery()
                End If
            End If
        Next i
        sqlCon.Close()
        sqlCon2.Close()
        Return UID
    End Function

    Sub toDeleteExistingUserMenu(ByVal UserId As Integer, ByVal CompanyID As Integer)
        Try

            Dim sqlCon As SqlConnection = New SqlConnection(strCon)
            Dim SqlCmd As New SqlCommand("Delete from UserAccess where UserID=" & UserId & " and CompanyID=" & CompanyID, sqlCon)
            sqlCon.Open()
            SqlCmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.ToString, 16, "Inventory Manager")
        End Try

    End Sub

    Public Sub UpdateUserProfile(ByVal UID As Integer, ByVal CompID As Integer, ByVal MenuXml As String)

        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim SqlCmd As New SqlCommand()
        sqlCon.Open()
        Dim sqlTrans As SqlTransaction
        sqlTrans = sqlCon.BeginTransaction
        SqlCmd.Connection = sqlCon
        SqlCmd.Transaction = sqlTrans

        Try
            Dim Xdoc As XmlDocument = New XmlDocument
            Xdoc.LoadXml(MenuXml)

            Dim MenuID As Integer
            Dim RootNode As XmlElement = Xdoc.DocumentElement
            Dim nodeList As XmlNodeList = RootNode.GetElementsByTagName("InvLine")
            Dim i As Integer
            For i = 0 To nodeList.Count - 1
                MenuID = Integer.Parse(nodeList.Item(i).ChildNodes(0).InnerXml())
                SqlCmd.CommandText = "Insert into UserAccess (UserID,CompanyID,MenuID) " & _
                    "Values(" & UID & "," & CompID & "," & MenuID & ")"
                SqlCmd.ExecuteNonQuery()
            Next i
            sqlTrans.Commit()
            sqlCon.Close()
        Catch ex As Exception
            sqlTrans.Rollback()
            sqlCon.Close()
        End Try
    End Sub
    Public Sub DeleteUserDetails(ByVal UID As Integer)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim SqlCmd As New SqlCommand()
        sqlCon.Open()
        strsql = "Update UserProfile set status='DELETED',TRFRD='N' where UserID=" & UID
        SqlCmd.CommandText = strsql
        SqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
End Class
